#!/bin/bash

ease_pwd=5ffe2cc0668dea99abbcdd2b52a72745

# 注：以下所有远程主机执行命令都是基于ssh 免密登陆的基础上实施的
#     所以执行此脚本前，请配置好涉及到主机的ssh免密配置

# 导出db到一个文件
# $1: dbhost
# $2: dbhost user
# $3: dbuser
# $4: dbpwd
# $5: dbname
export_db_to_file(){
    dbhost=$1           #db主机
    dbhost_user=$2      #db主机用户名
    dbuser=$3           #db用户名
    dbpwd=$4            #db密码
    dbname=$5           #db名

    db_file_name='/user/export.db'
   
    # 导出db 
    ssh -l $dbhost_user $dbhost "mysqldump -u$dbuser -p$dbpwd $dbname --skip-lock-tables > $db_file_name"
   
    # 返回文件名 
    echo $db_file_name
}

# 导入db
# $1: dbhost
# $2: dbhost user
# $3: dbuser
# $4: dbpwd
# $5: dbname
import_db_from_file(){
    dbhost=$1           #db主机
    dbhost_user=$2      #db主机用户名
    dbuser=$3           #db用户名
    dbpwd=$4            #db密码
    dbname=$5           #db名
    dbimport_file=$6

    echo '删除旧db'
    ssh -l $dbhost_user $dbhost "mysql -u$dbuser -p$dbpwd -e 'drop database if exists $dbname' "
    echo '创建新的空db'
    ssh -l $dbhost_user $dbhost "mysql -u$dbuser -p$dbpwd -e 'create database $dbname'"
    echo '导入新db'
    ssh -l $dbhost_user $dbhost "mysql -u$dbuser -p$dbpwd -e 'use $dbname; source $dbimport_file;'"
}


# 修改所有密码为123456
# $1: dbhost
# $2: dbhost user
# $3: dbuser
# $4: dbpwd
# $5: dbname
dbname_update_password(){
    dbhost=$1           #db主机
    dbhost_user=$2      #db主机用户名
    dbuser=$3           #db用户名
    dbpwd=$4            #db密码
    dbname=$5           #db名
    dbimport_file=$6

    echo '修改所有用户密码为123456'
    ssh -l $dbhost_user $dbhost "mysql -u$dbuser -p$dbpwd -e 'use $dbname; update xxx_table set password=\"$ease_pwd\";'"
}




# 下载文件
# $1: host
# $2: user
# $3: remote file path
# $4: local file path
download_file(){
    host=$1
    user=$2
    rfile_path=$3
    lfile_path=$4

    echo $user@$host:$rfile_path $lfile_path
    scp $user@$host:$rfile_path $lfile_path
}

# 上传文件
# $1: host
# $2: user
# $3: remote_file_path
# $4: local_file_path
upload_file(){
    host=$1
    user=$2
    rfile_path=$3
    lfile_path=$4

    echo $lfile_path $user@$host:$rfile_path
    scp  $lfile_path $user@$host:$rfile_path 
}

echo '导出db'
remote_db_filepath=$(export_db_to_file 'www.host.com' 'user' 'dbuser' 'dbpassword' 'dbname')

echo '下载文件'
local_db_file='export.db'
download_file 'www.host.com' 'user' $remote_db_filepath $local_db_file



echo '导入到local DB中'
echo '上传文件'
upload_file 'www.host.com' 'user' '/user/'$local_db_file $local_db_file

echo '导入db'
import_db_from_file 'www.host.com' 'user' 'dbuser' 'dbpassword' 'dbname' '/user/'$local_db_file

echo '修改密码'
dbname_update_password 'www.host.com' 'user' 'dbuser' 'dbpassword' 'dbname'

